package com.tro.generator.web;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSON;
import com.tro.common.util.date.DateUtil;
import com.tro.common.util.easyui.EUIDatagrid;
import com.tro.common.util.excel.ExcelModeUtils;
import com.tro.common.util.json.JsonUtil;
import com.tro.common.util.mybatis.SqlBuilder;
import com.tro.generator.entity.GenDicConfig;
import com.tro.generator.entity.GenTable;
import com.tro.generator.entity.GenTableCol;
import com.tro.generator.entity.GenTableColExample;
import com.tro.generator.entity.GenTableColExt;
import com.tro.generator.entity.GenTableColVO;
import com.tro.generator.service.GeneratorService;

import net.sf.json.JSONObject;

/**
 * 
 * @ClassName: GeneratorController
 * @Description: TODO(不生成Java代码版)
 * @author leeko
 * @date 2017年8月22日 下午12:39:14
 *
 */
@Controller
@RequestMapping("generator")
public class GeneratorController {
	protected final Logger logger = LoggerFactory.getLogger(this.getClass());
	@Autowired
	GeneratorService generatorService;

	// 首页
	@RequestMapping("index")
	public String index() {
		return "/gengrator/index";
	}

	// 返回表列表
	@RequestMapping("datagrid")
	@ResponseBody
	public EUIDatagrid getList() {
		EUIDatagrid grid = new EUIDatagrid();
		grid.setTotal(generatorService.countByExampleGenTable(null));
		grid.setRows(generatorService.selectByExampleGenTable(null));
		return grid;
	}

	@RequestMapping("tableCol")
	public String toUpdate(Model model, String id) {
		if (StringUtils.isBlank(id)) {
			return "404";
		}
		GenTable table = generatorService.selectByPrimaryKey(id);
		List<GenTableColVO> cols = generatorService.getCols(id);
		model.addAttribute("tableModel", table);
		model.addAttribute("cols", cols);
		return "/gengrator/indexForm";
	}

	@RequestMapping("buildAutoForm")
	@ResponseBody
	public String buildAutoForm(GenTable gTable, String baseTable, String dic, String tabExt, Model model) {
		logger.info("cols:" + baseTable);
		List<GenTableCol> cols = JsonUtil.jsonToList(baseTable, GenTableCol.class);
		List<GenTableColExt> colExts = JsonUtil.jsonToList(tabExt, GenTableColExt.class);
		List<GenDicConfig> dics = JsonUtil.jsonToList(dic, GenDicConfig.class);
		gTable.setTableTag(new BigDecimal("2"));
		gTable.setUpdateTime(new Date());
		String classPath = this.getClass().getClassLoader().getResource("/").getPath();
		String savePath = classPath.replaceAll("WEB-INF/classes/", "WEB-INF/views/gengrator/auto");
		try {
			generatorService.generatorSource(gTable, cols, dics, colExts, savePath);
		} catch (Exception e) {
			logger.error("自动生成代码失败", e);
			return "自动生成代码失败";
		}

		return "success";
	}

	@RequestMapping("auto/datagrid")
	@ResponseBody
	public EUIDatagrid getAutoList(String id, Integer page, Integer rows, String searchVal) {
		long startTime = System.currentTimeMillis();
		EUIDatagrid grid = new EUIDatagrid();
		boolean isPage = true;
		if (page == null && rows == null) {
			isPage = false;// 不分页
		}
		try {
			JSONObject search = new JSONObject();
			if (StringUtils.isNotBlank(searchVal)) {
				search = JsonUtil.strToJson(searchVal);
			}
			GenTable table = generatorService.selectByPrimaryKey(id);
			List<GenTableColVO> cols = generatorService.getCols(id);
			Map<String, Integer> jdbcTypes = getJdbcTypes(cols);
			if (table != null && StringUtils.isNotBlank(table.getGtableName())) {
				String sql = SqlBuilder.buildSelectSql(search, isPage, page, rows, jdbcTypes, table.getSqlVal());
				String countSql = SqlBuilder.builderCountSql(search, table.getSqlVal(), jdbcTypes);

				grid.setRows(generatorService.querySql(sql));
				grid.setTotal(generatorService.countSql(countSql));
			}
		} catch (Exception ex) {
			logger.error("查询失败", ex);
		}
		logger.info("执行耗时:" + (System.currentTimeMillis() - startTime) + "ms");

		return grid;
	}

	/**
	 * 
	 * @Title: getJdbcTypes
	 * @Description: TODO(需要修改)
	 * @param @param
	 *            cols
	 * @param @return
	 *            设定文件
	 * @return Map<String,Integer> 返回类型
	 * @throws @author
	 *             leeko
	 */
	public Map<String, Integer> getJdbcTypes(List<GenTableColVO> cols) {
		Map<String, Integer> jdbcTypes = new HashMap<String, Integer>();
		for (GenTableColVO col : cols) {
			if (col.getDataType().toUpperCase().indexOf("DATE") != -1) {
				jdbcTypes.put(col.getColName(), 3);
				continue;
			}
			if (col.getDataType().toUpperCase().indexOf("NUMBER") != -1) {
				jdbcTypes.put(col.getColName(), 1);
				continue;
			}
			jdbcTypes.put(col.getColName(), 2);
		}
		return jdbcTypes;
	}

	@RequestMapping("auto/toList")
	public String toList(String id) {
		return "/gengrator/auto/" + id + "_list";
	}

	@RequestMapping("auto/toAdd")
	public String toAdd(String id, String tid, Model model, String method) {
		long startTime = System.currentTimeMillis();
		Map<String, Object> initRecord = new HashMap<>();
		if (StringUtils.isBlank(tid)) {
			// 404
			return "404";
		}
		GenTable table = generatorService.selectByPrimaryKey(tid);
		if (table != null && StringUtils.isNotBlank(table.getGtableName())) {
			String tableName = table.getGtableName();
			List<GenTableCol> cols = new ArrayList<>();
			GenTableColExample search = new GenTableColExample();
			search.createCriteria().andTableIdEqualTo(tid);
			cols = generatorService.selectByExampleGenTableCol(search);
			for (GenTableCol c : cols) {
				initRecord.put(c.getColName(), null);
			}
			if (method.equals("add")) {
				// 新增
				model.addAttribute("record", initRecord);
			} else {
				// 查询
				Map<String, Object> record = new HashMap<>();
				List<Map<String, Object>> records = new ArrayList<>();
				records = generatorService.querySql("select * from " + tableName + " where 1=1 and id='" + id + "'");
				if (records != null && records.size() > 0) {
					model.addAttribute("record", records.get(0));
				} else {
					model.addAttribute("record", initRecord);
				}
			}
			model.addAttribute("tid", tid);
			logger.info("执行耗时:" + (System.currentTimeMillis() - startTime) + "ms");
			return "/gengrator/auto/" + tid + "_form";
		} else {
			return "404";
		}
	}

	@RequestMapping("auto/add")
	@ResponseBody
	public String doAdd(@RequestParam Map<String, Object> map, String BASE_FORM_LEEKO_TID, HttpServletRequest request) {
		// 转换为json格式

		long startTime = System.currentTimeMillis();
		if (StringUtils.isBlank(BASE_FORM_LEEKO_TID)) {
			return "数据操作失败";
		}
		GenTable table = generatorService.selectByPrimaryKey(BASE_FORM_LEEKO_TID);
		List<GenTableColVO> cols = generatorService.getCols(BASE_FORM_LEEKO_TID);
		Map<String, Integer> jdbcTypes = getJdbcTypes(cols);
		if (table != null && StringUtils.isNotBlank(table.getGtableName())) {
			// 自己去拼接SQL
			JSONObject jsonSession = null;
			if (StringUtils.isNotBlank(table.getSessionVal())) {
				Object session = request.getSession().getAttribute(table.getSessionVal());
				if (session != null) {
					// 转换为JSON
					jsonSession = JSONObject.fromObject(session);
				}
			}
			try {
				String sql = "";
				if (map.get("ID") != null&&StringUtils.isNotBlank(map.get("ID").toString())) {
					// 编辑

					for (GenTableColVO vo : cols) {
						if (StringUtils.isNotBlank(vo.getDefaultVal())) {
							if (vo.getDefaultVal().indexOf("update_${") != -1) {
								if (vo.getDefaultVal().indexOf("update_${session.") != -1) {
									String key = vo.getDefaultVal().replace("update_${session.", "");
									key = vo.getDefaultVal().replace("}", "").trim();
									if (jsonSession != null) {
										Object val = jsonSession.get(key);
										map.put(vo.getColName(), val);
									}
								} else if (vo.getDefaultVal().indexOf("update_${sysdate}") != -1) {
									map.put(vo.getColName(), DateUtil.simpleFormat(new Date()));
								} else {
									logger.error("不能识别的表达式:" + vo.getDefaultVal());
								}
							}

						}
					}
					sql = SqlBuilder.buildUpdateSql(map, table.getGtableName(), jdbcTypes);

				} else {
					// 新增
					for (GenTableColVO vo : cols) {
						if (StringUtils.isNotBlank(vo.getDefaultVal())) {
							if (vo.getDefaultVal().indexOf("insert_${") != -1) {
								if (vo.getDefaultVal().indexOf("insert_${session.") != -1) {
									String key = vo.getDefaultVal().replace("insert_${session.", "");
									key = vo.getDefaultVal().replace("}", "").trim();
									if (jsonSession != null) {
										Object val = jsonSession.get(key);
										map.put(vo.getColName(), val);
									}
								} else if (vo.getDefaultVal().indexOf("insert_${sysdate}") != -1) {
									map.put(vo.getColName(), DateUtil.simpleFormat(new Date()));
								} else {
									logger.error("不能识别的表达式:" + vo.getDefaultVal());
								}
							}

						}
					}
					sql = SqlBuilder.buildInsertSql(map, table.getGtableName(), jdbcTypes,
							Integer.parseInt(table.getGkeyPolicy().toString()), null, table.getSeqVal());
				}
				generatorService.querySql(sql);
			} catch (Exception ex) {
				logger.error("数据操作失败", ex);
				return "数据操作失败";
			}
		}
		logger.info("执行耗时:" + (System.currentTimeMillis() - startTime) + "ms");
		return "success";
	}
	@RequestMapping("del")
	@ResponseBody
	public String doGenDel(String id) {
		if (StringUtils.isBlank(id)) {
			return "数据操作失败";
		}
		String classPath = this.getClass().getClassLoader().getResource("/").getPath();
		String savePath = classPath.replaceAll("WEB-INF/classes/", "WEB-INF/views/gengrator/auto");
		generatorService.deleteGenTable(id,savePath);
		return "success";
	}
	
	
	/**
	 * 模板导出
	 * @throws IOException 
	 * @throws InvalidFormatException 
	 * */
	@RequestMapping("auto/exportModel")
	@ResponseBody
	public String expModel(@RequestParam("expfile") MultipartFile file) throws InvalidFormatException, IOException{
		ExcelModeUtils em=new ExcelModeUtils();
		
		return "";
	}
	
	
	
	/**
	 * 导出
	 * @throws IOException 
	 * */
	@RequestMapping("auto/export")
	@ResponseBody
	public String export(String tid,HttpServletRequest req,HttpServletResponse resp,String searchVal) throws IOException {
		UUID uid=UUID.randomUUID();
		String path = req.getSession().getServletContext()
				.getRealPath("\\file\\"+uid+".xls");
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet0");
		//获取Table表
		GenTable table = generatorService.selectByPrimaryKey(tid);
		List<GenTableColVO> cols = generatorService.getCols(tid);
		//设置表头
		HSSFRow row = sheet.createRow(0);
		row.createCell(0).setCellValue(table.getGtableComment());
		//构造表头
		row = sheet.createRow(1);
		int i=0;
		HSSFCell cell=null;
		//列顺序list
		List<String> seq=new ArrayList<String>();
		for(GenTableColVO v:cols){
			cell=row.createCell(i);
			cell.setCellValue(v.getColComment());
			seq.add(v.getColName());
			i++;
		}
		//内容查询遍历
		List<Map<String, Object>> list=new ArrayList<Map<String,Object>>();
		JSONObject search = new JSONObject();
		if (StringUtils.isNotBlank(searchVal)) {
			search = JsonUtil.strToJson(searchVal);
		}
		//自定义查询SQL
		String sqlValue=table.getSqlVal()==null?"":table.getSqlVal();
		Map<String, Integer> jdbcTypes = getJdbcTypes(cols);
		if (table != null && StringUtils.isNotBlank(table.getGtableName())) {
			String sql = SqlBuilder.buildSelectSqlNoPage(search,table.getGtableName(),jdbcTypes,sqlValue);
			list=generatorService.querySql(sql);
		}
		
		//列数据
		int j=2;
		for(Map<String, Object> map:list){
			row = sheet.createRow(j);
			for(int n=0;n<seq.size();n++){
				cell=row.createCell(n);
				cell.setCellValue(map.get(seq.get(n)).toString());
			}
			j++;
		}
		
		StringBuilder path1=new StringBuilder(path);
		OutputStream out = new FileOutputStream(path);
		wb.write(out);
		out.close();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("success", true);
		String download = req.getScheme() + "://"
				+ req.getServerName() + ":"
				+ req.getServerPort() + req.getContextPath()
				+ "/file/" + path1.substring(path1.lastIndexOf("\\") + 1);
		map.put("obj", download);
        return JSON.toJSONString(map);
	}
	
	
	@RequestMapping("copy")
	@ResponseBody
	public String doGenCopy(String id) {
		if (StringUtils.isBlank(id)) {
			return "数据操作失败";
		}
		generatorService.copyGenTable(id);
		return "success";
	}
	@RequestMapping("auto/del")
	@ResponseBody
	public String doDel(String tid, String id) {
		if (StringUtils.isBlank(tid)) {
			return "数据操作失败";
		}
		GenTable table = generatorService.selectByPrimaryKey(tid);
		if (table == null || table.getId() == null) {
			return "数据操作失败";
		} else {
			String[] arrIds = id.split(",");
			List<Object> ids = new ArrayList<>();
			for (String s : arrIds) {
				ids.add(s);
			}
			Integer jdbcType = 0;
			if (table.getGkeyPolicy().compareTo(2L) == 0) {
				jdbcType = 1;
			} else {
				jdbcType = 2;
			}
			try {
				String sql = SqlBuilder.buildDelSql(ids, table.getGtableName(), jdbcType);
				generatorService.delSql(sql);
			} catch (Exception ex) {
				logger.error("删除数据失败", ex);
				return "系统错误，删除数据失败";

			}
		}
		return "success";
	}

	@RequestMapping("auto/getSelectJson")
	@ResponseBody
	public String getSelectJson(String dicId) {
		if (StringUtils.isBlank(dicId)) {
			return "";
		}
		GenDicConfig dic = generatorService.selectByKeyDic(dicId);
		if (StringUtils.isNotBlank(dic.getDicTable()) && StringUtils.isNotBlank(dic.getDicTableKey())
				&& StringUtils.isNotBlank(dic.getDicTableText())) {
			StringBuilder sb=new StringBuilder();
			sb.append("select ");
			sb.append(dic.getDicTableKey());
			sb.append(" id,");
			sb.append(dic.getDicTableKey());
			sb.append(" text from ");
			sb.append(dic.getDicTable());
			List<Map<String, Object>> result=generatorService.querySql(sb.toString());
			return JSON.toJSONString(result);
		}else if(StringUtils.isNotBlank(dic.getDicJson())){
			return dic.getDicJson();
		}
		return "";
	}

	/**
	 * 
	 * @Title: synchronizationOracle
	 * @Description: TODO(同步数据库)
	 * @param @return
	 *            设定文件
	 * @return Map<String,Object> 返回类型
	 * @throws @author
	 *             leeko
	 */
	@RequestMapping("synchronization")
	@ResponseBody
	public Map<String, Object> synchronizationOracle() {
		Map<String, Object> result = new HashMap<>();
		result.put("code", 0);
		result.put("message", "同步数据库表失败");
		try {
			generatorService.refTables();
			result.put("code", 1);
			result.put("message", "同步数据库表成功");
		} catch (Exception ex) {
			logger.error("同步数据库表失败", ex);
		}
		return result;
	}

}
